from Users import Users
from Dashboards import Dashboards
from Widgets import Widgets
from Grids import Grids
from UserGrid import UserGrid
from Groups import Groups
from GridGroup import GridGroup
from WidgetGroup import WidgetGroup
from Columns import Columns
from Variables import Variables
from Operations import Operations
from UserGroup import UserGroup
from DashboardGroup import DashboardGroup
from UserOperation import UserOperation
from OperationGroup import OperationGroup
from CommentOperation import CommentOperation
from Comments import Comments
from CommentGrid import CommentGrid
from sqlalchemy.orm.exc import NoResultFound
from sqlalchemy import Table, MetaData, Column, Sequence, ForeignKey
from sqlalchemy.types import Integer, String
from __init__ import Base


class MainTable:

    def __init__(self, database):
        self.database = database
        Base.metadata.create_all(self.database.bind)
        print Users.__table__
        print Dashboards.__table__
        print Widgets.__table__

    def checkUser(self, username):
        query = self.database.query(Users.username).filter(Users.username == username)
        try:
            query.one()
        except NoResultFound, e:
            return False
        return True

    def checkGroup(self, group):
        query = self.database.query(Groups.name).filter(Groups.name == group)
        try:
            query.one()
        except NoResultFound, e:
            return False
        return True

    def getOperations(self, username):
        return self.database.query(Operations.name, Operations.location).filter(Users.username == username).all()

    def getTables(self, grid):
        return self.database.query(Grids.name, Grids.database).filter(Users.username == username).all()

    def getGroupsForUser(self, username):
        return self.database.query(Group.name).filer(Users.username == username).all()

    def getGroups(self):
        return self.database.query(Group.name).all()

    def getUser(self, username):
        query = self.database.query(Users.username, Users.name, Users.database).filter(Users.username == username)
        try:
            user = query.one()
        except NoResultFound, e:
            return None
        return user

    def getUsers(self, name=None, email=None, username=None):
        query = self.database.query(Users.username, Users.name, Users.database)
        if name != None:
            query.filter(Users.name.like(name))
        if email != None:
            query.filter(Users.email.like(email))
        if username != None:
            query.filter(Users.username.like(username))
        return query.all()

    def getWidget(self, username, title, location):
        widgetid = self.database.query(Widgets.id).filter(Users.username == username).filter(Dashboards.title == title).filter(Widgets.location == location).one()[0]
        widget = self.database.query(Widgets.view, Columns.name, Variables.name, Variables.value).filter(Widgets.id == widgetid).all()

    def insertUser(self, username):
        self.database.add(Users(username, None, "../../databases/" + username, None, None))

    def createDashboard(self, title, username):
        userid = self.database.query(Users.id).filter(Users.username == username).one()[0]
        self.database.add(Dashboards(title, userid))

    def createWidget(self, view, location, username, title, table, columns, variables):
        dashboardid = self.database.query(Dashboards.id).filter(and_(Users.username == username, Dashboards.title == title)).one()[0]
        gridid = self.database.query(Grids.id).filter(and_(Users.username == username, Grids.name == table)).one()[0]
        widget = self.database.add(Widgets(view, location, dashboardid, gridid))
        for column in columns:
            self.database.add(Columns(widget.id, column))
        for variable in variables:
            self.database.add(Variables(widget.id, variable))

    def createGrid(self, username, name, database):
        userid = self.database.query(Users.id).filter(Users.username == username).one()[0]
        grid = self.database.add(Grids(name, database))
        self.database.add(UserGrid(userid, grid.id))
        

    def createOperation(self, name, location, username):
        userid = self.database.query(Users.id).filter(Users.username == username).one()[0]
        operation = self.database.add(Operations(name, location, userid))
        self.database.add(UserOperation(userid, operation.id))

    def createComment(self, comment, username, grid=None, operation=None):
        userid = self.database.query(Users.id).filter(Users.username == username).one()[0]
        record = self.database.add(Comments(comment, userid)) 
        if grid != None:
            gridid = self.database.query(Grids.id).filter(and_(User.id == userid, Grids.name == grid)).one()[0]
            self.database.add(CommentGrid(record.id, gridid))
        elif operation != None:
            operationid = self.database.query(Operations.id).filter(and_(User.id == userid, Operations.name == operation)).one()[0]
            self.database.add(record.id, operationid)

    def createGroup(self, name):
        self.database.add(Groups(group))

    def addTableToGroup(self, table, group, username):
        groupid = self.database.query(Groups.id).filter(Groups.name == group).one()[0]
        tableid = self.database.query(Grids.id).filter(and_(Grids.name == table, Users.username == username)).one()[0]
        self.database.add(GridGroup(tableid, groupid))

    def addOperationToGroup(self, operation, group, username):
        groupid = self.database.query(Groups.id).filter(Groups.name == group).one()[0]
        operationid = self.database.query(Operation.id).filter(and_(Operations.name == operation, Users.username == username)).one()[0]
        self.database.add(OperationGroup(operaationid, groupid))

    def addUserToGroup(self, username, group):
        userid = self.database.query(Users.id).filter(Users.username == username).one()[0]
        groupid = self.database.query(Groups.id).filer(Groups.name == group).one()[0]
        self.database.add(UserGroup(userid, groupid))
        
